package by.bsu.touragency.db.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import by.bsu.touragency.db.beans.Eat;
import by.bsu.touragency.db.connection.DBConnector;

public class EatDAO extends AbstractDAO {
	public static final String SQL_EAT_CREATE = " INSERT INTO tourAgencyDB.eat (type) VALUES (?);";
	public static final String SQL_EAT_CREATE_GET_ID = "SELECT * FROM tourAgencyDB.eat  WHERE type = ? ;";
	public static final String SQL_EAT_READ_BY_ID = "SELECT * FROM tourAgencyDB.eat WHERE (id= ?);";
	public static final String SQL_EAT_READ_BY_TYPE = "SELECT * FROM tourAgencyDB.eat WHERE (type= ?);";
	public static final String SQL_EAT_UPDATE = "UPDATE tourAgencyDB.eat  SET type = ? WHERE (id = ?);";
	public static final String SQL_EAT_DELETE = "DELETE FROM tourAgencyDB.eat WHERE id = ?;";
	public static final String SQL_EAT_IS_EXIST_TYPE = "SELECT COUNT(*) AS rowcount FROM tourAgencyDB.eat " + "WHERE (type = ?);";
	public static final String SQL_EAT_IS_USED = " SELECT COUNT(*) AS rowcount FROM tourAgencyDB.hotel WHERE eatId = ? ;";

	private static Logger logger = Logger.getLogger("log");

	public boolean isExistEatName(Eat eat) {
		logger.info("Is meal with this type exist");
		try {// 1
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_EAT_IS_EXIST_TYPE);
			int n;
			try {// 3
				statement.setString(1, eat.getType());
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}// 3
			if (resultSet2 != null) {
				resultSet2.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (n > 0) {
				logger.debug("This type already exist");
				return true;
			} else {
				logger.debug("It is new type");
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		}
	}

	private boolean isUsed(int id) {
		logger.info("Is this meal type in use");
		try {// 1
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_EAT_IS_USED);
			int n;
			try {// 3
				statement.setLong(1, id);
				resultSet2 = statement.executeQuery();
				n = -2;
				resultSet2.next();
				n = resultSet2.getInt("rowcount");
			} finally {
			}

			if (n > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
			return false;
		} catch (Exception e) {
			logger.error("Undefined exeption");
			return false;
		} finally {
			// close();
		}
	}

	// C
	public Eat create(Eat eat) {
		if (!isExistEatName(eat)) {
			logger.info("Create new meal type");
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_EAT_CREATE);
				statement.setString(1, eat.getType());
				statement.executeUpdate();

				statement = connector
						.getPreparedStatement(SQL_EAT_CREATE_GET_ID);
				statement.setString(1, eat.getType());
				resultSet = statement.executeQuery();

				if (resultSet.next()) {
					eat.setId(resultSet.getInt("id"));
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		} else {
			eat = readByType(eat);
		}
		return eat;
	}

	// R
	public Eat readById(int id) {
		logger.info("Read meal type by id");
		Eat eat = new Eat();
		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_EAT_READ_BY_ID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				eat.setId(resultSet.getInt("id"));
				eat.setType(resultSet.getString("type"));
				eat.setAbout(resultSet.getString("about"));
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return eat;
	}

	public Eat readByType(Eat eat) {
		logger.info("Read meal type by type");

		try {
			connector = new DBConnector();
			PreparedStatement statement = connector
					.getPreparedStatement(SQL_EAT_READ_BY_TYPE);
			statement.setString(1, eat.getType());
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				eat.setId(resultSet.getInt("id"));
				eat.setAbout(resultSet.getString("about"));
			}
		} catch (SQLException e) {
			logger.error("SQL exeption");
		} catch (Exception e) {
			logger.error("Undefined exeption");
		} finally {
			logger.debug("Close connection");
			close();
		}
		return eat;
	}

	public void deleteById(int id) {
		if (!isUsed(id)) {
			logger.info("Delete meal type");
			try {
				connector = new DBConnector();
				PreparedStatement statement = connector
						.getPreparedStatement(SQL_EAT_DELETE);
				statement.setInt(1, id);
				statement.executeUpdate();
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("SQL exeption");
			} catch (Exception e) {
				logger.error("Undefined exeption");
			} finally {
				logger.debug("Close connection");
				close();
			}
		}
	}
}
